
package com.dats.db;

import com.dats.entity.BaseObj;
import com.dats.entity.CacheBean;
import com.dats.util.LogUtil;

import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @author sig
 * @since 2016/9/25
 */
public class DatS {
    private static Map<String, Map<String, Method>> classNameSetMap =
            new ConcurrentHashMap<>();
    private static Map<String, Map<String, Method>> classNameGetMap =
            new ConcurrentHashMap<>();

    public static Connection getConnection(String alias) {
        try {
            return DriverManager.getConnection(alias);
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
            return null;
        }
    }

    public static <T extends CacheBean> void saveDelList(String alias,
                                                         List<T> list) {
        if (null == list) return;

        for (T tmp : list) {

            if (null != tmp) saveDel(alias, tmp);
        }
    }

    public static <T extends CacheBean> void saveDel(String alias,
                                                     T t) {
        if (null == t) return;
        NameKeyUnit unit = NameMap.getNameKeyUnit(t.getClass());

        if (null == unit) return;
        String sql = getSaveDelSql(alias, unit.getTableName(),
                t, unit.getSaveKey());
        update(alias, sql);
    }

    public static <T extends CacheBean> String getSaveDelSql(String alias,
                                                             String tableName,
                                                             T t,
                                                             String fieldName) {
        try {
            Method method = getGetMethod(alias, tableName,
                    t.getClass(), fieldName);
            Object value = method.invoke(t);
            return "UPDATE `" + tableName + "` SET `del` = '1' WHERE `" +
                    fieldName.toLowerCase() + "` = '" + value + "'";
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
            return null;
        }
    }

    public static void delete(String alias, String tableName) {
        String sql = getDelSql(tableName);
        update(alias, sql);
    }

    public static String getDelSql(String tableName) {
        return "DELETE FROM `" + tableName + "` WHERE `del` = '1';";
    }

    public static <T extends CacheBean> void saveArray(String alias,
                                                       T[] array) {
        if (null == array) return;

        for (T tmp : array) {

            if (null != tmp) saveObj(alias, tmp);
        }
    }

    public static <T extends CacheBean> void saveList(String alias,
                                                      Collection<? extends CacheBean> c) {
        if (null == c) return;
        List<T> list = new ArrayList(c);

        for (T tmp : list) {

            if (null != tmp) saveObj(alias, tmp);
        }

    }

    public static <T extends CacheBean> void saveObj(String alias, T t) {
        if (null == t) return;
        NameKeyUnit unit = NameMap.getNameKeyUnit(t.getClass());

        if (null == unit) return;
        saveObj(alias, t, unit.getTableName(), unit.getSaveKey());
    }


    public static <T extends CacheBean> void saveObj(String alias,
                                                     T t,
                                                     String tableName,
                                                     String saveKey) {

        if (null == t) return;
        String sql = getSql(alias, t, tableName, saveKey);
        update(alias, sql);
        t.setDefaultStatus();
    }

    public static <T extends CacheBean> String getSql(String alias,
                                                      T t,
                                                      String tableName,
                                                      String saveKey) {

        if (null == tableName || "".equals(tableName.trim()))
            return null;
        Map<String, Object> map = getFieldValueMap(alias, tableName, t);
        String sql = null;

        if (1 == t.getStatus()) {
            sql = getInsertSql(tableName, map);
        } else if (2 == t.getStatus()) {
            sql = getUpdateSql(tableName, map, saveKey);
        }

        if (null != sql) LogUtil.console.info(sql);
        return sql;
    }

    public static String getUpdateSql(String tableName,
                                      Map<String, Object> map,
                                      String saveKey) {
        if (null == map) return null;
        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE `").append(tableName).append("` SET ");
        String clause = null;

        for (Map.Entry<String, Object> entry : map.entrySet()) {

            if (null == entry) continue;
            String filedName = entry.getKey();
            String fieldValue = String.valueOf(entry.getValue());

            if (null == fieldValue) {
                fieldValue = "";
            } else {
                fieldValue = fieldValue.replaceAll("'", "\"");
            }

            if (filedName.equalsIgnoreCase(saveKey)) {
                clause = " WHERE `" + filedName + "` = '" + fieldValue + "'";
            } else {
                builder.append("`").append(filedName).append("` = '")
                        .append(fieldValue).append("', ");
            }
        }
        builder.deleteCharAt(builder.lastIndexOf(","));
        builder.deleteCharAt(builder.lastIndexOf(" "));

        if (null != clause) builder.append(clause);
        builder.append(";");
        return builder.toString();
    }

    public static String getInsertSql(String tableName,
                                      Map<String, Object> map) {
        if (null == map) return null;
        StringBuilder keyBuilder = new StringBuilder();
        keyBuilder.append("INSERT INTO `").append(tableName).append("` (");
        StringBuilder valueBuilder = new StringBuilder();
        valueBuilder.append(") VALUES (");

        for (Map.Entry<String, Object> entry : map.entrySet()) {

            if (null == entry) continue;
            String filedName = entry.getKey();
            keyBuilder.append("`").append(filedName).append("`, ");
            String fieldValue = String.valueOf(entry.getValue());

            if (null == fieldValue) {
                fieldValue = "";
            } else {
                fieldValue = fieldValue.replaceAll("'", "\"");
            }
            valueBuilder.append("'").append(fieldValue).append("', ");
        }
        keyBuilder.deleteCharAt(keyBuilder.lastIndexOf(","));
        keyBuilder.deleteCharAt(keyBuilder.lastIndexOf(" "));
        valueBuilder.deleteCharAt(valueBuilder.lastIndexOf(","));
        valueBuilder.deleteCharAt(valueBuilder.lastIndexOf(" "));
        keyBuilder.append(valueBuilder).append(");");
        return keyBuilder.toString();
    }

    public static <T extends CacheBean> Map<String, Object> getFieldValueMap(String alias,
                                                                             String tableName,
                                                                             T t) {
        Map<String, Object> map = new HashMap<>();

        try {
            Map<String, Method> getMethodMap =
                    getGetMethodMap(alias, tableName, t.getClass());

            if (null == getMethodMap) return null;

            for (Map.Entry<String, Method> entry :
                    getMethodMap.entrySet()) {
                String fieldName = entry.getKey();
                Method method = entry.getValue();
                Object value = method.invoke(t);

                if (value instanceof Boolean)
                    value = (Boolean) value ? 1 : 0;
                map.put(fieldName, value);
            }
            return map;
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
            return null;
        }
    }

    public static <T> String getLoadSql(Class<T> clazz) {
        NameKeyUnit unit = NameMap.getNameKeyUnit(clazz);

        if (null == unit) return null;

        if (unit.isBase())
            return "SELECT * FROM `" + unit.getTableName() + "`;";
        return "SELECT * FROM `" + unit.getTableName() + "` WHERE `del` = '0';";
    }

    public static <T> String getLoadSql(Class<T> clazz, long value) {
        NameKeyUnit unit = NameMap.getNameKeyUnit(clazz);

        if (null == unit) return null;
        return "SELECT * FROM `" + unit.getTableName() +
                "` WHERE `" + unit.getLoadKey().toLowerCase() + "` = '" + value +
                "' AND `del` = '0';";
    }

    public static <T> String getLoadSql(Class<T> clazz, long value,
                                        int num) {
        NameKeyUnit unit = NameMap.getNameKeyUnit(clazz);

        if (null == unit) return null;
        return "SELECT * FROM `" + unit.getTableName() + num +
                "` WHERE `" + unit.getLoadKey() + "` = '" + value +
                "' AND `del` = '0';";
    }

    public static <T extends CacheBean> T loadBeanByValue(String alias,
                                                          Class<T> clazz,
                                                          long value) {
        String sql = getLoadSql(clazz, value);
        return loadBean(alias, clazz, sql);
    }

    public static <T extends CacheBean> List<T> loadBeanListByValue(String alias,
                                                                    Class<T> clazz,
                                                                    long value) {
        String sql = getLoadSql(clazz, value);
        List<T> list = loadBeanList(alias, clazz, sql);

        for (T tmp : list) {

            if (null != tmp) tmp.setDefaultStatus();
        }
        return list;
    }

    public static <T extends CacheBean> List<T> loadBeanListByValue(String alias,
                                                                    Class<T> clazz,
                                                                    long value,
                                                                    int num) {
        String sql = getLoadSql(clazz, value, num);
        List<T> list = loadBeanList(alias, clazz, sql);

        for (T tmp : list) {

            if (null != tmp) tmp.setDefaultStatus();
        }
        return list;
    }

    public static <T extends CacheBean> List<T> loadBeanList(String alias,
                                                             Class<T> clazz) {
        String sql = getLoadSql(clazz);
        List<T> list = loadBeanList(alias, clazz, sql);

        for (T tmp : list) {

            if (null != tmp) tmp.setDefaultStatus();
        }
        return list;
    }

    public static <T extends CacheBean> T loadBean(String alias,
                                                   Class<T> clazz) {
        String sql = getLoadSql(clazz);
        T t = loadBean(alias, clazz, sql, null, null, null);

        if (null != t) t.setDefaultStatus();
        return t;
    }

    public static <T extends CacheBean> T loadBean(String alias,
                                                   Class<T> clazz,
                                                   String sql) {
        T t = loadBean(alias, clazz, sql, null, null, null);

        if (null != t) t.setDefaultStatus();
        return t;
    }

    public static <T extends BaseObj> List<T> loadBaseList(String alias,
                                                           Class<T> clazz) {
        String sql = getLoadSql(clazz);
        return loadBeanList(alias, clazz, sql);
    }

    public static <T> List<T> loadBeanList(String alias,
                                           Class<T> clazz,
                                           String sql) {
        List<T> list = new ArrayList<>();
        loadBean(alias, clazz, sql, list, null, null);
        return list;
    }

    public static <T extends CacheBean> Map<Object, T> loadBeanMap(String alias,
                                                                   Class<T> clazz,
                                                                   String sql,
                                                                   String fieldName) {
        Map<Object, T> map = new HashMap<>();
        loadBean(alias, clazz, sql, null, map, fieldName);

        for (T tmp : map.values()) {

            if (null != tmp) tmp.setDefaultStatus();
        }
        return map;
    }

    public static <T extends CacheBean> Map<Object, T> loadBeanMap(String alias,
                                                                   Class<T> clazz,
                                                                   long value,
                                                                   String fieldName) {
        NameKeyUnit unit = NameMap.getNameKeyUnit(clazz);

        if (null == unit) return null;
        Map<Object, T> map = new HashMap<>();
        String sql = getLoadSql(clazz, value);
        loadBean(alias, clazz, sql, null, map, fieldName);

        for (T tmp : map.values()) {

            if (null != tmp) tmp.setDefaultStatus();
        }
        return map;
    }

    public static <T extends CacheBean> Map<Object, T> loadBeanMap(String alias,
                                                                   Class<T> clazz) {
        NameKeyUnit unit = NameMap.getNameKeyUnit(clazz);

        if (null == unit) return null;
        Map<Object, T> map = new HashMap<>();
        String sql = getLoadSql(clazz);
        loadBean(alias, clazz, sql, null, map, unit.getLoadKey());

        for (T tmp : map.values()) {

            if (null != tmp) tmp.setDefaultStatus();
        }
        return map;
    }

    public static <T extends CacheBean> Map<Object, T> loadBeanConcurrentMap(String alias,
                                                                             Class<T> clazz,
                                                                             String sql,
                                                                             String fieldName) {
        Map<Object, T> map = new ConcurrentHashMap<>();
        loadBean(alias, clazz, sql, null, map, fieldName);

        for (T tmp : map.values()) {

            if (null != tmp) tmp.setDefaultStatus();
        }
        return map;
    }

    public static <T> T loadBean(String alias, Class<T> clazz,
                                 String sql, List<T> list,
                                 Map<Object, T> map, String loadKey) {
        LogUtil.console.info(sql);
        T t = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection(alias);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            String tableName = metaData.getTableName(1);
            Map<String, Method> setMethodMap = getSetMethodMap(
                    alias, tableName, clazz);

            while (rs.next()) {
                t = clazz.newInstance();
                Object obj = null;

                for (int pos = 1; pos <= columnCount; pos++) {
                    String fieldName = metaData.getColumnLabel(pos);
                    Method method = setMethodMap.get(fieldName);

                    if (null == method) {
                        LogUtil.console.error("Oh no, the table:[" +
                                tableName + "] field:[" + fieldName +
                                "] method is null.");
                        continue;
                    }
                    Object args;
                    String type = method.getParameterTypes()[0].getName();

                    switch (type) {
                        case "byte":
                            args = rs.getByte(fieldName);
                            break;
                        case "short":
                            args = rs.getShort(fieldName);
                            break;
                        case "boolean":
                            args = rs.getBoolean(fieldName);
                            break;
                        default:
                            args = rs.getObject(fieldName);
                            break;
                    }
                    method.invoke(t, args);

                    if (fieldName.equalsIgnoreCase(loadKey))
                        obj = args;
                }

                if (null == list && null == map) break;

                if (null != list) list.add(t);

                if (null != map) map.put(obj, t);
            }
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
        } finally {
            closeResultSet(rs);
            closeStatement(stmt);
            closeConnection(conn);
        }
        return t;
    }

    public static int loadInt(String alias, String sql) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection(alias);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            if (rs.first())
                return rs.getInt(1);
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
        } finally {
            closeResultSet(rs);
            closeStatement(stmt);
            closeConnection(conn);
        }
        return 0;
    }

    public static List<Integer> loadIntList(String alias, String sql) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Integer> list = new ArrayList<>();

        try {
            conn = getConnection(alias);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs.next())
                list.add(rs.getInt(1));
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
        } finally {
            closeResultSet(rs);
            closeStatement(stmt);
            closeConnection(conn);
        }
        return list;
    }

    public static long loadLong(String alias, String sql) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection(alias);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            if (rs.first())
                return rs.getLong(1);
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
        } finally {
            closeResultSet(rs);
            closeStatement(stmt);
            closeConnection(conn);
        }
        return 0L;
    }

    public static List<Long> loadLongList(String alias, String sql) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Long> list = new ArrayList<>();

        try {
            conn = getConnection(alias);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs.next())
                list.add(rs.getLong(1));

        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
        } finally {
            closeResultSet(rs);
            closeStatement(stmt);
            closeConnection(conn);
        }
        return list;
    }

    public static String loadString(String alias, String sql) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection(alias);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            if (rs.first())
                return rs.getString(1);
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
        } finally {
            closeResultSet(rs);
            closeStatement(stmt);
            closeConnection(conn);
        }
        return "";
    }

    public static List<String> loadStringList(String alias, String sql) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<String> list = new ArrayList<>();

        try {
            conn = getConnection(alias);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs.next())
                list.add(rs.getString(1));
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
        } finally {
            closeResultSet(rs);
            closeStatement(stmt);
            closeConnection(conn);
        }
        return list;
    }

    public static Map<String, Object> loadMap(String alias,
                                              String sql) {
        return loadMap(alias, sql, null);
    }

    public static List<Map<String, Object>> loadMapList(String url,
                                                        String sql) {
        List<Map<String, Object>> list = new ArrayList<>();
        loadMap(url, sql, list);
        return list;
    }

    private static Map<String, Object> loadMap(String alias,
                                               String sql,
                                               List<Map<String, Object>> list) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Map<String, Object> map = null;

        try {
            conn = getConnection(alias);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();

            while (rs.next()) {
                map = new HashMap<>();

                for (int pos = 1; pos <= metaData.getColumnCount(); pos++)
                    map.put(metaData.getColumnName(pos), rs.getObject(pos));

                if (null == list) break;
                list.add(map);
            }
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
        } finally {
            closeResultSet(rs);
            closeStatement(stmt);
            closeConnection(conn);
        }
        return map;
    }


    public static synchronized void update(String alias, String sql) {
        if (null == sql || "".equals(sql.trim())) return;
        Connection conn = null;
        Statement stmt = null;

        try {
            conn = getConnection(alias);
            stmt = conn.createStatement();
            stmt.execute(sql);
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
        } finally {
            closeStatement(stmt);
            closeConnection(conn);
        }
    }

    public static void updateBatch(String alias, String[] sqls) {
        for (String sql : sqls) {

            if (null != sql && !"".equals(sql.trim()))
                update(alias, sql);
        }
    }

    public static synchronized void updateBatch(String alias, List<String> sqlList) {
        if (null == sqlList) return;

        for (String sql : sqlList) {

            if (null != sql && !"".equals(sql.trim()))
                update(alias, sql);
        }

    }

    public static Method getGetMethod(String alias,
                                      String tableName,
                                      Class<?> clazz,
                                      String fieldName) {
        Map<String, Method> map = getGetMethodMap(alias, tableName, clazz);
        return map.get(fieldName);
    }

    public static Map<String, Method> getSetMethodMap(String alias,
                                                      String tableName,
                                                      Class<?> clazz) {
        Map<String, Method> map = classNameSetMap.get(clazz.getName());

        if (null == map) {
            loadMethodMap(alias, tableName, clazz);
            map = classNameSetMap.get(clazz.getName());
        }
        return map;
    }

    public static Map<String, Method> getGetMethodMap(String alias,
                                                      String tableName,
                                                      Class<?> clazz) {
        Map<String, Method> map = classNameGetMap.get(clazz.getName());

        if (null == map) {
            loadMethodMap(alias, tableName, clazz);
            map = classNameGetMap.get(clazz.getName());
        }
        return map;
    }

    public static synchronized void loadMethodMap(String alias, String tableName,
                                                  Class<?> clazz) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM " + tableName + " LIMIT 0";

        try {
            conn = getConnection(alias);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            Map<String, Method> getMap = new HashMap<>();
            Map<String, Method> setMap = new HashMap<>();
            Method[] methods = clazz.getMethods();
            Map<String, String> filedNameTypeMap = getFiledTypeMap(rs);

            for (Map.Entry<String, String> entry :
                    filedNameTypeMap.entrySet()) {

                if (null == entry) continue;
                String filedName = entry.getKey();

                for (Method method : methods) {

                    if (null == method) continue;

                    if (!method.getName().startsWith("get") &&
                            !method.getName().startsWith("set") &&
                            !method.getName().startsWith("is"))
                        continue;
                    String name;

                    if (method.getName().startsWith("is")) {
                        name = method.getName().substring(2);
                    } else {
                        name = method.getName().substring(3);
                    }

                    if (!filedName.equalsIgnoreCase(name)) continue;

                    if (method.getName().startsWith("set")) {
                        setMap.put(filedName, method);
                    } else {
                        getMap.put(filedName, method);
                    }
                }
            }
            classNameSetMap.put(clazz.getName(), setMap);
            classNameGetMap.put(clazz.getName(), getMap);
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
        } finally {
            closeResultSet(rs);
            closeStatement(stmt);
            closeConnection(conn);
        }
    }

    public static Map<String, String> getFiledTypeMap(ResultSet rs) {
        Map<String, String> map = new HashMap<>();

        try {
            ResultSetMetaData metaData = rs.getMetaData();

            for (int pos = 1, columnCount = metaData
                    .getColumnCount(); pos <= columnCount; pos++)
                map.put(metaData.getColumnName(pos),
                        metaData.getColumnTypeName(pos));
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
            return null;
        }
        return map;
    }

    public static void closeConnection(Connection conn) {
        if (null != conn) try {
            conn.close();
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
        }
    }

    public static void closeStatement(Statement stmt) {
        if (null != stmt) try {
            stmt.close();
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
        }
    }

    public static void closeResultSet(ResultSet rs) {
        if (null != rs) try {
            rs.close();
        } catch (Exception e) {
            LogUtil.console.error(e.getMessage(), e);
        }
    }
}
